﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;
using AdministrationWeb;

namespace AdministrationWeb
{
    public partial class User_Import : System.Web.UI.Page
    {
        private DataTable m_dtCSV;
        private Int32 m_iColumnCount;
        private const string CONNECTION_STRING_NAME = "ApplicationConnectionString";

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnStudentUpload_Click(object sender, EventArgs e)
        {
            try
            {
                lbl_Error.Text = "";
                if (studentUploader.PostedFile != null) //Make sure a file was selected
                {
                    //Build our table and add to DB from the file selected
                    PopulateDataTableFromUploadedFile(studentUploader.PostedFile.InputStream);
                    ctlGridView.DataSource = m_dtCSV;
                    ctlGridView.DataBind();
                }
            }
            catch (Exception exception)
            {

                lbl_Error.Text = "Error importing students: " + exception;
            }

        }

        private void PopulateDataTableFromUploadedFile(System.IO.Stream strm)
        {
            try
            {
                var srdr = new System.IO.StreamReader(strm);
                String strLine = String.Empty;
                Int32 iLineCount = 0;
                do
                {
                    strLine = srdr.ReadLine();
                    if (strLine == null)
                    {
                        break;
                    }
                    if (0 == iLineCount++)
                    {
                        m_dtCSV = this.CreateDataTableForCSVData(strLine);
                    }
                    else
                    {
                        this.AddDataRowToTable(strLine, m_dtCSV);
                    }
                } while (true);
            }
            catch (Exception exception)
            {
                lbl_Error.Text = "Error importing students: " + exception;
            }
        }

        private DataTable CreateDataTableForCSVData(String strLine)
        {
            var dt = new DataTable("CSVTable");
            try
            {
                //Create a datatable
                String[] strVals = strLine.Split(new char[] { ',' });
                m_iColumnCount = strVals.Length;

                //Create all of the columns we expect from the csv file
                dt.Columns.Add("Graduation Year");
                dt.Columns.Add("Username");
                dt.Columns.Add("Password");
                dt.Columns.Add("First Name");
                dt.Columns.Add("Last Name");
                dt.Columns.Add("Email");

                return dt;
            }
            catch (System.IndexOutOfRangeException)
            {
                lbl_Error.Text = "Error importing students: Not enough columns in input file.";
                return dt;
            }
            catch (Exception exception)
            {
                lbl_Error.Text = "Error importing students: " + exception;
                return dt;
            }
        }

        private DataRow AddDataRowToTable(String strCSVLine, DataTable dt)
        {
            MySqlConnection conn = null;
            try
            {
                String[] strVals = strCSVLine.Split(new char[] { ',' });
                Int32 iTotalNumberOfValues = strVals.Length;

                string insertUpdateStatement = "INSERT INTO users "
                    + "(graduation_year, username, password, permission, creation_date, first_name, last_name, email, last_modified_date, last_modify_user_id, created_user_id) "
                    + "VALUES (?graduation_year, ?username, ?password, ?permission, ?creation_date, ?first_name, ?last_name, ?email, ?creation_date, ?last_modify_user_id, ?last_modify_user_id) "
                    + "ON DUPLICATE KEY UPDATE graduation_year=?graduation_year, password=?password, permission=?permission, first_name=?first_name, last_name=?last_name, "
                    + "email=?email, last_modified_date=?creation_date, last_modify_user_id=?last_modify_user_id";

                conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString);
                MySqlCommand command = new MySqlCommand(insertUpdateStatement, conn);

                command.Parameters.AddWithValue("?graduation_year", "20" + strVals[3].Substring(0, 2));


                command.Parameters.AddWithValue("?last_name", strVals[0]);
                command.Parameters.AddWithValue("?first_name", strVals[1]);
                command.Parameters.AddWithValue("?password", HashData.HashLogin(strVals[3], strVals[2]));
                command.Parameters.AddWithValue("?username", strVals[3]);
                command.Parameters.AddWithValue("?email", strVals[4]);
                command.Parameters.AddWithValue("?permission", "S");
                command.Parameters.AddWithValue("?creation_date", DateTime.Now);
                command.Parameters.AddWithValue("?last_modify_user_id", MySession.Current.userUserID);


                conn.Open();
                command.ExecuteNonQuery();
                DataRow drow = dt.NewRow();

                drow["Graduation Year"] = "20" + strVals[3].Substring(0, 2);
                drow["Username"] = strVals[3];
                drow["Password"] = "-";
                drow["First Name"] = strVals[1];
                drow["Last Name"] = strVals[0];
                drow["Email"] = strVals[4];

                dt.Rows.Add(drow);

                return drow;
            }
            catch (System.IndexOutOfRangeException)
            {
                lbl_Error.Text = "Error importing students: The format of the file is incorrect.<br />";
                lbl_Error.Text += "Correct format is:<br />";
                lbl_Error.Text += "Last Name, First Name, Student ID, Usercoade, Email Adress<br />";
                return null;
            }
            catch (Exception ex)
            {
                lbl_Error.Text = "Error importing students: " + ex;
                return null;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }
    }
}